/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package personal.utility;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import personal.obj.*;

/**
 *
 * @author PHATNBSE61057
 */
public class DBUtility {

    private final String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private final String sourceURL = "jdbc:sqlserver://localhost:1433;databaseName=TolkienBook;user=sa;password=123456";
    private final String destinationURL = "jdbc:sqlserver://localhost:1433;databaseName=XMLProject;user=sa;password=123456";

    public Connection makeConnection(String URL) {
        try {
            Class.forName(driver);
            Connection con = DriverManager.getConnection(URL);
            return con;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Category> loadCategoryFromSourceDB() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Category cate = null;
        List<Category> result = new ArrayList<>();
        try {
            con = makeConnection(sourceURL);
            String query = "SELECT * FROM Category";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            while (rs.next()) {
                String cateID = rs.getString("CategoryID");
                String cateName = rs.getString("CategoryName");
                String cateLink = rs.getString("CategoryLink");
                cate = new Category(cateID, cateName, cateLink, cateName);
                result.add(cate);
            }
            return result;
        } catch (Exception e) {
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public boolean insertCategoryIntoDestinationDB(List<Category> categories) {
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = makeConnection(destinationURL);
            String query = "";
            for (Category category : categories) {
                query = "INSERT INTO Category (CategoryID,CategoryName,CategoryLink) VALUES (?,?,?)";
                pst = con.prepareStatement(query);
                pst.setString(1, category.getCategoryID());
                pst.setString(2, category.getCategoryName());
                pst.setString(3, category.getCategoryLink());
                pst.executeUpdate();
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public List<Book> loadBookFromSourceDB() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        List<Book> result = new ArrayList<>();
        try {
            con = makeConnection(sourceURL);
            String query = "SELECT * FROM Book";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            while (rs.next()) {
                String bookID = rs.getString("BookID");
                String cateID = rs.getString("CategoryID");
                String bookName = rs.getString("BookName");
                String bookLink = rs.getString("BookLink");
                String bookThumb = rs.getString("BookThumb");
                String bookImg = rs.getString("BookImg");
                String bookPrice = rs.getString("BookPrice");
                String bookPages = rs.getString("BookPages");
                String bookFormat = rs.getString("BookFormat");
                String bookAuthor = rs.getString("BookAuthor");
                String bookPublishedDate = rs.getString("BookPublishedDate");
                String bookISBN = rs.getString("BookISBN");
                String bookDetailedEdition = rs.getString("BookDetailedEdition");
                String bookDescription = rs.getString("BookDescription");
                book = new Book(bookID, cateID, bookName, bookLink, bookThumb,
                        bookImg, bookPrice, bookPages, bookFormat, bookAuthor,
                        bookPublishedDate, bookISBN, bookDetailedEdition,
                        bookDescription, bookPages);
                result.add(book);
            }
            return result;
        } catch (Exception e) {
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public boolean insertBookIntoDestinationDB(List<Book> books) {
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = makeConnection(destinationURL);
            String query = "";
            for (Book book : books) {
                query = "INSERT INTO Book (BookID,CategoryID,BookName,BookLink,"
                        + "BookThumb,BookImg,BookPrice,BookPages,BookFormat,"
                        + "BookAuthor,BookPublishedDate,BookISBN,"
                        + "BookDetailedEdition,BookDescription) "
                        + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                pst = con.prepareStatement(query);
                pst.setString(1, book.getBookID());
                pst.setString(2, book.getCategoryID());
                pst.setString(3, book.getBookName());
                pst.setString(4, book.getBookLink());
                pst.setString(5, book.getBookThumb());
                pst.setString(6, book.getBookImg());
                pst.setString(7, book.getBookPrice());
                pst.setString(8, book.getBookPages());
                pst.setString(9, book.getBookFormat());
                pst.setString(10, book.getBookAuthor());
                pst.setString(11, book.getBookPublishedDate());
                pst.setString(12, book.getBookISBN());
                pst.setString(13, book.getBookDetailedEdition());
                pst.setString(14, book.getBookDescription());
                pst.executeUpdate();
            }
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public boolean transferData() {
        try {
            List<Category> listCategory = new ArrayList<>();
            listCategory = loadCategoryFromSourceDB();
            boolean resultCate = insertCategoryIntoDestinationDB(listCategory);
            if (resultCate) {
                List<Book> listBook = new ArrayList<>();
                listBook = loadBookFromSourceDB();
                boolean resultBook = insertBookIntoDestinationDB(listBook);
                if (resultBook) {
                    return true;
                }
            }
        } catch (Exception e) {
            return false;

        }
        return false;
    }
}
